using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Diagnostics;

namespace ORM
{
    public class PersonDb
    {
        public static OdbcConnection DBConnection
        {
            get
            {
                try
                {
                    if (_odbcConnection == null) 
                        _odbcConnection = new OdbcConnection();
                }
                catch (OdbcException exc) 
                {
                    Trace.TraceError(" --> Error: {0}", exc.Message);
                }
                return _odbcConnection;
            }
            set
            {
                try
                {
                    _odbcConnection = value;
                    ConnectionString = _odbcConnection.ConnectionString;
                }
                catch (OdbcException exc) 
                {
                    Trace.TraceError(" --> Error: {0}", exc.Message);
                }
            }
        }
        
        public static string TableName { get; set; }
        public static string ConnectionString { get; set; }
        private static OdbcConnection _odbcConnection;

        public static ConnectionState Connect()
        {
            if (DBConnection.State != ConnectionState.Open)
                DBConnection.Open();
            return DBConnection.State;
        }
        public static ConnectionState Disconnect()
        {
            //DBConnection.Dispose(); 
            DBConnection.Close();
            return DBConnection.State;
        }

        public int Id;

        private Person _person;
        public Person ObjectLink
        {
            get
            {
                if (_person == null)
                {
                    _person = new Person {DBObject = this};
                    _person.Load();
                }
                return _person;
            }
            set
            {
                if (_person == null) 
                    _person = value;
            }
        }

        public PersonDb() : this(-1) { }

        public PersonDb(int id) { Id = id; }

        public PersonDb(Person person)
            : this(person.FirstName, person.LastName, person.Age)
        {
            ObjectLink = person;
        }

        public PersonDb(string firstName, string lastName, int age)
        {
            if (DBConnection == null) return;
            Connect();
            MakeAQuery("INSERT INTO " + TableName +
                       " (Firstname, Lastname, Age)" +
                       " VALUES ('" + firstName + "', '" + lastName + "', " + age + ")");
            var arr = MakeAQuery("SELECT @@IDENTITY");
            Id = int.Parse(arr[1][0]);
            Disconnect();
        }

        public void Remove()
        {
            Connect();
            MakeAQuery("DELETE FROM " + TableName + " WHERE ID = " + Id);
            _person = null;
            Id = -1;
            Disconnect();
        }

        public void Save(Person person)
        {
            Connect();
            if (Id >= 0)
            {
                MakeAQuery("UPDATE " + TableName +
                    " SET Firstname = '" + person.FirstName + "', " +
                    " Lastname = '" + person.LastName + "', " +
                    " Age = " + person.Age +
                    " WHERE ID = " + Id);
            }
            Disconnect();
        }

        public Person Load()
        {
            if (Id < 0) return null;
            Connect();
            var ret = MakeAQuery(
                " SELECT Firstname, Lastname, Age " +
                " FROM " + TableName +
                " WHERE ID = " + Id + ";");
            if (ret.Length != 2) return null;
            var person = new Person(ret[1][0], ret[1][1], int.Parse(ret[1][2]));
            Disconnect();
            return person;
        }

        public static string[][] MakeAQuery(string text)
        {
            var command = DBConnection.CreateCommand();
            command.CommandText = text;
            OdbcDataReader reader;
            try 
            { 
                reader = command.ExecuteReader(); 
            }
            catch (Exception exc) 
            {
                Trace.TraceError(" --> Error: {0}", exc.Message);
                return null; 
            }
            var listReturn = new List<string[]>();
            var arrRow = new string[reader.VisibleFieldCount];
            for (var i = 0; i < reader.VisibleFieldCount; ++i)
                arrRow[i] = reader.GetName(i);
            listReturn.Add(arrRow);
            while (reader.Read())
            {
                arrRow = new string[reader.VisibleFieldCount];
                for (var i = 0; i < reader.VisibleFieldCount; ++i)
                    arrRow[i] = reader[i].ToString();
                listReturn.Add(arrRow);
            }
            return listReturn.ToArray();
        }
    }
}